﻿using Model;
using System;
using System.Data;

namespace BLL
{
    public class Lc_ProcessBLL : BaseBLL
    {
        public Lc_ProcessBLL()
        {
        }

        public Lc_ProcessBLL(Service service)
        {
            this.Service = service;
        }

        public DataTable GetAllInfo(string Style, string param)
        {
            string sql = string.Format(@"SELECT
                lcp.N_PROCESS_ID,
                lcp.C_PROCESS_NAME,
                lcp.N_PROCESS_STATE,
                lcp.N_PROCESS_OPERATOR_ID,
                lcp.D_PROCESS_OPERATOIN_TIME,
                lcp.C_PROCESS_REQUESTION_COM,
                lcp.TIPTIME,
                lcp.COMPANYID,
                sysu.USER_NAME,
                sysc.CMY_NAME
            FROM
                Lc_Process lcp,
                Sys_Company sysc,
                Sys_User sysu
            WHERE
                lcp.COMPANYID = sysc.CMY_ID
                AND lcp.N_PROCESS_OPERATOR_ID = sysu.USER_ID
                AND lcp.N_PROCESS_STATE = 1");

            if (Style == "ProcessName")
            {
                sql += " AND lcp.C_PROCESS_NAME='" + param + "'";
            }
            sql += " ORDER BY lcp.N_PROCESS_ID DESC";
            DataTable dt = Query(sql);
            return dt;
        }

        public Lc_Process GetInfoById(string id)
        {
            var sql = string.Format(@"SELECT
	N_PROCESS_ID,
	C_PROCESS_NAME,
	N_PROCESS_STATE,
	N_PROCESS_OPERATOR_ID,
	D_PROCESS_OPERATOIN_TIME,
	C_PROCESS_REQUESTION_COM,
	TIPTIME,
	COMPANYID
FROM
	LC_PROCESS
WHERE
	N_PROCESS_ID = {0}", id);//N_PROCESS_STATE = 1 AND

            DataTable dt = Query(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                var entity = new Lc_Process();
                entity.N_PROCESS_ID = Convert.ToDecimal(dt.Rows[0]["N_PROCESS_ID"]);
                entity.C_PROCESS_NAME = Convert.ToString(dt.Rows[0]["C_PROCESS_NAME"]);
                entity.N_PROCESS_STATE = Convert.ToDecimal(dt.Rows[0]["N_PROCESS_STATE"]);
                entity.N_PROCESS_OPERATOR_ID = Convert.ToDecimal(dt.Rows[0]["N_PROCESS_OPERATOR_ID"]);
                entity.D_PROCESS_OPERATOIN_TIME = Convert.ToDateTime(dt.Rows[0]["D_PROCESS_OPERATOIN_TIME"]);
                entity.C_PROCESS_REQUESTION_COM = dt.Rows[0]["C_PROCESS_REQUESTION_COM"] == DBNull.Value ? null : Convert.ToString(dt.Rows[0]["C_PROCESS_REQUESTION_COM"]);
                entity.TIPTIME = dt.Rows[0]["TIPTIME"] == DBNull.Value ? -1 : Convert.ToDecimal(dt.Rows[0]["TIPTIME"]);
                entity.COMPANYID = Convert.ToDecimal(dt.Rows[0]["COMPANYID"]);
                return entity;
            }
            return null;
        }

        /// <summary>
        /// 查询有效流程
        /// </summary>
        /// <param name="id"></param>
        /// <param name="operName"></param>
        /// <returns></returns>
        public DataTable GetInfoByIdTab(string id, string operName)
        {
            string sql = string.Format(@"select *
  from lc_process t
 where t.n_process_state = 1
   and t.n_process_id =" + id + " order by t.n_process_id");

            DataTable dt = Query(sql, "", "GetInfoByIdTab", "查询有效流程", operName);
            return dt;
        }

        public int INSERT(Lc_Process model, string operName)
        {
            model.N_PROCESS_ID = GetSequence("N_PROCESS_ID");
            //已修改
            string sql = string.Format(@"INSERT INTO Lc_PROCESS(
N_PROCESS_ID,
C_PROCESS_NAME,
N_PROCESS_STATE,
N_PROCESS_OPERATOR_ID,
D_PROCESS_OPERATOIN_TIME,
C_PROCESS_REQUESTION_COM,
TIPTIME,
COMPANYID)
VALUES({0}, '{1}', {2}, {3}, TO_DATE('{4}','YYYY-MM-DD HH24:MI:SS'), '{5}', {6},{7})
", model.N_PROCESS_ID, model.C_PROCESS_NAME, model.N_PROCESS_STATE, model.N_PROCESS_OPERATOR_ID, model.D_PROCESS_OPERATOIN_TIME.ToString("yyyy-MM-dd HH:mm:ss"),
 model.C_PROCESS_REQUESTION_COM, model.TIPTIME, model.COMPANYID);

            int count = NoQuery(sql, operName);
            return count;
        }

        public int UPDATE(Lc_Process model, string operName)
        {
            var sql = string.Format(@"UPDATE LC_PROCESS
SET

	C_PROCESS_NAME = '{1}',
	N_PROCESS_STATE = {2},
	N_PROCESS_OPERATOR_ID = {3},
	D_PROCESS_OPERATOIN_TIME = TO_DATE('{4}','YYYY-MM-DD HH24:MI:SS'),
	C_PROCESS_REQUESTION_COM = '{5}',
	TIPTIME = {6},
	COMPANYID = {7}
WHERE N_PROCESS_ID = {0}",
                         model.N_PROCESS_ID,
                         model.C_PROCESS_NAME,
                         model.N_PROCESS_STATE,
                         model.N_PROCESS_OPERATOR_ID,
                         model.D_PROCESS_OPERATOIN_TIME.ToString("yyyy-MM-dd HH:mm:ss"),
                         model.C_PROCESS_REQUESTION_COM,
                         model.TIPTIME, model.COMPANYID);

            int count = NoQuery(sql, operName);
            return count;
        }

        public int DELETE(string ids, string operName)
        {
            if (string.IsNullOrEmpty(ids))
            {
                return 0;
            }
            var sql = string.Format(@"DELETE FROM LC_Process WHERE N_PROCESS_ID IN(" + ids + ")");

            int count = NoQuery(sql, operName);
            return count;
        }

        public Lc_Process GetProcessByCom(string keyCom, string operName)
        {
            string sql = string.Format(@"SELECT
	N_PROCESS_ID,
	C_PROCESS_NAME,
	N_PROCESS_STATE,
	N_PROCESS_OPERATOR_ID,
	D_PROCESS_OPERATOIN_TIME,
	C_PROCESS_REQUESTION_COM,
	TIPTIME,
	COMPANYID
FROM
	LC_PROCESS
WHERE
	N_PROCESS_STATE = 1 AND
	C_PROCESS_REQUESTION_COM = '{0}'", keyCom);
            DataTable dt = Query(sql);
            Lc_Process model = new Lc_Process();
            foreach (DataRow dr in dt.Rows)
            {
                model.N_PROCESS_ID = decimal.Parse(dr["N_PROCESS_ID"].ToString());
                model.C_PROCESS_NAME = dr["C_PROCESS_NAME"].ToString();
                model.N_PROCESS_STATE = decimal.Parse(dr["N_PROCESS_STATE"].ToString());
                model.N_PROCESS_OPERATOR_ID = decimal.Parse(dr["N_PROCESS_OPERATOR_ID"].ToString());
                model.D_PROCESS_OPERATOIN_TIME = DateTime.Parse(dr["D_PROCESS_OPERATOIN_TIME"].ToString());
                model.C_PROCESS_REQUESTION_COM = dr["C_PROCESS_REQUESTION_COM"].ToString();
                model.TIPTIME = decimal.Parse(dr["TIPTIME"].ToString());
                model.COMPANYID = decimal.Parse(dr["COMPANYID"].ToString());
            }
            return model;
        }

        public int GetRecordCount(string whereStr)
        {
            string sql = string.Format(@"SELECT count(1)
FROM
    THKQS.Lc_Process lcp,
    THKQS.Sys_Company sysc,
    THKQS.Sys_User sysu
WHERE
    lcp.COMPANYID = sysc.CMY_ID
    AND lcp.N_PROCESS_OPERATOR_ID = sysu.USER_ID
   ");//AND lcp.N_PROCESS_STATE = 1

            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += whereStr;
            }
            return GetCount(sql);
        }

        public DataTable GetListByPage(string whereStr, string orderby, int startIndex, int endIndex)
        {
            string sql = string.Format(@"SELECT * FROM (SELECT  ROW_NUMBER() OVER(ORDER BY lcp." + orderby + @") as r,
    lcp.N_PROCESS_ID,
    lcp.C_PROCESS_NAME,
    CASE lcp.N_PROCESS_STATE
    WHEN 0 THEN
    '停用'
    ELSE
    '使用'
    END N_PROCESS_STATE,
    lcp.N_PROCESS_OPERATOR_ID,
    to_char(lcp.D_PROCESS_OPERATOIN_TIME,'yyyy-mm-dd HH24:MI:SS') D_PROCESS_OPERATOIN_TIME,
    lcp.C_PROCESS_REQUESTION_COM,
    lcp.TIPTIME,
    lcp.COMPANYID,
    sysu.USER_NAME,
    sysc.CMY_NAME
FROM
    THKQS.Lc_Process lcp,
    THKQS.Sys_Company sysc,
    THKQS.Sys_User sysu
WHERE
    lcp.COMPANYID = sysc.CMY_ID
    AND lcp.N_PROCESS_OPERATOR_ID = sysu.USER_ID
    ");//AND lcp.N_PROCESS_STATE = 1

            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += whereStr;
            }

            sql += @" ) TT";
            if (startIndex >= 2)
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + "+1 AND " + startIndex + "*" + endIndex + "";
            }
            else
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + " AND " + startIndex + "*" + endIndex + "";
            }
            return Query(sql);
        }

        /// <summary>
        /// 查询流程
        /// </summary>
        /// <returns></returns>
        public DataTable GetProcByState(string whr, string loginName)
        {
            string sql = @"select * from lc_process t  where 1=1 " + whr;
            DataTable dt = Query(sql, "", "GetProcByState", "查询有效流程", loginName);
            return dt;
        }

        public int GetRecordCount1(string com_name, string begin_time, string end_time)
        {
            string sql = string.Format(@"select count(1)
          from (select count(decode(b.n_business_state, 0, b.n_business_id)) zhuxiao,
                       count(decode(b.n_business_state, 1, b.n_business_id)) zhengzaibanli,
                       count(decode(b.n_business_state, 2, b.n_business_id)) guidang,
                       p.c_process_name names,
                       p.n_process_id idss,
                       c.cmy_id cmid,
                       c.cmy_name cinames

                  from qs_business b,
                       lc_process p,
                       sys_company c,
                       (select lc_t.n_queue_id,
                               lc_t.n_process_id,
                               b.n_business_id,
                               b.n_business_name,
                               c.cmy_id cids,
                               c.cmy_name
                          from lc_transaction lc_t,
                               qs_business    b,
                               sys_company    c

                         where lc_t.n_queue_id in
                               (select n_queue_id_left
                                  from lc_condition lcc
                                 where lcc.n_condition_state = 2
                                   and lcc.n_queue_id_left in
                                       (select lcq.n_queue_id
                                          from lc_queue lcq
                                         where lcq.n_queue_id in
                                               (select lct.n_queue_id
                                                  from lc_transaction lct
                                                 group by n_queue_id)))
                           and b.n_business_id = lc_t.n_business_id
                           and c.cmy_id in
                               (select su.user_company
                                  from sys_user su
                                 where ',' || lc_t.c_transactor_id || ',' LIKE
                                       '%,' || su.user_id || ',%')) bbls
                 where b.n_process_id = p.n_process_id
                   and bbls.n_business_id = b.n_business_id
                   and c.cmy_id = bbls.cids ");

            if (com_name != "")
            {
                sql += string.Format(@" and c.cmy_id in ({0})", com_name);
            }
            sql += @" and b.n_business_operator_time between to_date('" + begin_time + " 00:00:00','yyyy-MM-dd hh24:mi:ss') and to_date('" + end_time + @" 23:59:59','yyyy-MM-dd hh24:mi:ss')
group by p.c_process_name,c.cmy_name,
p.n_process_id,c.cmy_id)";

            return GetCount(sql);
        }

        public DataTable GetListByPage1(string com_name, string begin_time, string end_time, string orderby, int startIndex, int endIndex)
        {
            string sql = string.Format(@"select *
  from (select ROW_NUMBER() OVER(ORDER BY " + orderby + @") as r,
       cinames,
       names,
       zhuxiao,
       zhengzaibanli,
       guidang,
       idss,
       cmid
  from (select count(decode(b.n_business_state, 0, b.n_business_id)) zhuxiao,
               count(decode(b.n_business_state, 1, b.n_business_id)) zhengzaibanli,
               count(decode(b.n_business_state, 2, b.n_business_id)) guidang,
               p.c_process_name names,
               p.n_process_id idss,
               c.cmy_id cmid,
               c.cmy_name cinames

          from qs_business b, lc_process p, sys_company c,
          (
               select lc_t.n_queue_id,lc_t.n_process_id,b.n_business_id,b.n_business_name,c.cmy_id cids,c.cmy_name
                  from lc_transaction lc_t,
                  qs_business b ,
                  sys_company c

                 where lc_t.n_queue_id in
                       (select n_queue_id_left
                          from lc_condition lcc
                         where lcc.n_condition_state = 2
                           and lcc.n_queue_id_left in
                               (select lcq.n_queue_id
                                  from lc_queue lcq
                                 where lcq.n_queue_id in
                                       (select lct.n_queue_id
                                          from lc_transaction lct
                                         group by n_queue_id)))
                  and b.n_business_id=lc_t.n_business_id
                  and c.cmy_id in (select su.user_company from sys_user su where ','||lc_t.c_transactor_id ||','LIKE '%,'||su.user_id ||',%')
                ) bbls
         where b.n_process_id = p.n_process_id
           and bbls.n_business_id=b.n_business_id
           and c.cmy_id=bbls.cids ");

            if (com_name != "")
            {
                sql += string.Format(@" and c.cmy_id in ({0})", com_name);
            }
            sql += @" and b.n_business_operator_time between to_date('" + begin_time + " 00:00:00','yyyy-MM-dd hh24:mi:ss') and to_date('" + end_time + @" 23:59:59','yyyy-MM-dd hh24:mi:ss')
group by p.c_process_name,c.cmy_name,
p.n_process_id,c.cmy_id)";

            sql += @" ) TT";
            if (startIndex >= 2)
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + "+1 AND " + startIndex + "*" + endIndex + "";
            }
            else
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + " AND " + startIndex + "*" + endIndex + "";
            }
            return Query(sql);
        }
    }
}